package Worker;

import DBHelper.DBInits;

import java.io.PrintWriter;
import java.sql.*;
import java.util.ArrayList;

public class WorkerDao {
    public  Connection getCon(){
        Connection con=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/jwglxt?serverTimezone=UTC&characterEncoding=utf-8";
            con= DriverManager.getConnection(url,"root","root");
        }catch (Exception e){
            e.printStackTrace();
        }
        return con;
    }


    public String getLogin(String workerNo,String workPas,String post ){    //用于登陆验证
        //获取数据库连接对象
        Connection con= DBInits.getCon();
        //SQL语句
        String sql="select workerName from tb_worker where workerNo=? and workPas=? and post=?";
        try {
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,workerNo);
            ps.setString(2,workPas);
            ps.setString(3,post);
            ResultSet rs=ps.executeQuery();
            if(rs.next()){  //如果结果集非空，为正数
                String workerName=rs.getString("workerName");
                return workerName;
            }else {
                return null;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }

        return null;
    }
    //依据学号查询
    public WorkerEnity getWorker(String workerNo){
        Connection con=this.getCon();
        //定义sql语句
        String sql="select workerNo,workerName,sex,workPas,post from tb_worker where workerNo=?";

        try {
            //创建prepareStatement
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,workerNo);
            ResultSet rs=ps.executeQuery();//执行sql语句
            WorkerEnity worker=new WorkerEnity();
            while (rs.next()){
                worker.setWorkerNo(rs.getString("workerNo"));
                worker.setWorkerName(rs.getString("workerName"));
                worker.setSex(rs.getString("sex"));
                worker.setPost(rs.getString("post"));
                worker.setWorkpas(rs.getString("WorkPas"));}
            rs.close();
            ps.close();
            con.close();
            return worker;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }

    }
    //新增员工
    public int addWorker(String workerNo,String workerName,String sex,String workPas,String post){
        int row=0;
        Connection con=this.getCon();
        //sql语句
        String sql="insert into tb_worker (workerNo,workerName,sex,workPas,post) values(?,?,?,?,?)";
        try {
            //创建ps对象
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,workerNo);
            ps.setString(2,workerName);
            ps.setString(3,sex);
            ps.setString(4,workPas);
            ps.setString(5,post);
            //执行sql语句
            row=ps.executeUpdate();
            ps.close();
            con.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return row;
    }

    //修改员工
    public int updateWorker(String workerNo,String workerName,String sex,String workPas,String post){
        Connection con=this.getCon();
        try {
            //sql语句
            String sql="update tb_worker set workerName=?,sex=?,workPas=?,post=? where workerNo=?";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,workerName);
            ps.setString(2,sex);
            ps.setString(3,workPas);
            ps.setString(4,post);
            ps.setString(5,workerNo);
            //执行sql语句
            int row=ps.executeUpdate();
            ps.close();
            con.close();
            return row;
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }
    //删除员工
    public int deleteWorker(String workerNo){
        //创建数据库连接对象
        Connection con=this.getCon();
        //sql语句
        String sql="delete from tb_worker where workerNo=?";
        try {
            //创建PS对象
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,workerNo);
            //执行sql
            int row=ps.executeUpdate();
            ps.close();
            con.close();
            return row;
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }
    //查询教师工号
    public ArrayList<String> getWorkerNo(String post){
        Connection con=this.getCon();
        ArrayList<String> lists=new ArrayList<>();
        try {
            String sql="select workerNo from tb_worker where post=?";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,post);
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                String workerNo=rs.getString("workerNo");
                lists.add(workerNo);
            }
            return lists;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
}
